﻿using Microsoft.Office.Interop.Excel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace LunarExcelAddIn
{
    public partial class FindForm : Form
    {
        public FindForm()
        {
            InitializeComponent();

            toolTip1.SetToolTip(tbxQueryLines, "相当于点击“查找”按钮。");

            //读取所有工作表的名称

            int i = 0;
            int selSheetIndex = 0;
            foreach (Worksheet sht in Globals.ThisAddIn.Application.Sheets)
            {
                i++;
                cmbSheetNames.Items.Add(sht.Name);
                if (Globals.ThisAddIn.Application.ActiveSheet == sht)
                {
                    selSheetIndex = i;
                    sourceSheet = sht;
                }
            }

            cmbSheetNames.SelectedIndex = selSheetIndex - 1;

            FillCellsToListBox();
        }

        private Worksheet sourceSheet = null;

        private void cmbSheetNames_SelectedIndexChanged(object sender, EventArgs e)
        {
            sourceSheet = Globals.ThisAddIn.Application.Sheets[cmbSheetNames.SelectedIndex + 1];
            var usedColumnCount = sourceSheet.UsedRange.Columns.Count;
            if (usedColumnCount <= 0 || sourceSheet.UsedRange.Count <= 0)
            {
                MessageBox.Show($"表 【{cmbSheetNames.Text}】 中没有可供查询的数据！", "Excel", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }

            numericUpDown1.Maximum = sourceSheet.UsedRange.Row + sourceSheet.UsedRange.Rows.Count - 1;
            numericUpDown1.Value = sourceSheet.UsedRange.Row;
            //FillCellsToListBox();//没必要
        }

        private void numericUpDown1_ValueChanged(object sender, EventArgs e)
        {
            FillCellsToListBox();
        }

        private void FillCellsToListBox()
        {
            cklstColumns.Items.Clear();
            cmbFindColumn.Items.Clear();

            if (sourceSheet == null) return;
            if (cmbSheetNames.SelectedIndex < 0) return;
            if (numericUpDown1.Value < sourceSheet.UsedRange.Row) return;

            Range titleCells = sourceSheet.Cells[numericUpDown1.Value, 1];
            titleCells = Globals.ThisAddIn.Application.Intersect(sourceSheet.UsedRange, titleCells.EntireRow);

            if (titleCells != null && titleCells.Count > 0)
            {
                foreach (Range cell in titleCells)
                {
                    if (cell == null) continue;//可能存在合并单元格

                    string columnTitleText;
                    if (cell.Value == null) columnTitleText = "[空列标题]";
                    else columnTitleText = cell.Value.ToString();

                    cklstColumns.Items.Add(columnTitleText);
                    cmbFindColumn.Items.Add(columnTitleText);
                }
            }

            if (cmbFindColumn.Items.Count > 0)
            {
                cmbFindColumn.SelectedIndex = 0;
            }
        }

        /* 已无必要。
        private void btnFind_Click(object sender, EventArgs e)
        {
            if (sourceSheet == null)
            {
                MessageBox.Show("当前没有打开可供查询的工作表！", "Excel", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }

            if (sourceSheet.UsedRange.Count <= 0)
            {
                MessageBox.Show("选定的表中没有可供查询的数据！", "Excel", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }

            if (cklstColumns.CheckedItems.Count <= 0)
            {
                MessageBox.Show("请选中至少一列作为查找结果！", "Excel", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }

            //从哪一列开始，用户选择这一列必须准确，否则结果就不全。
            int startRowIndex = (int)numericUpDown1.Value + 1;//标题行不算。
            int startColumnIndex = sourceSheet.UsedRange.Column;
            int findColumnIndex = startColumnIndex + cmbFindColumn.SelectedIndex;

            //先取出要出现在结果中的列标题
            List<int> columnIndexes = new List<int>();
            for (int i = 0; i < cklstColumns.Items.Count; i++)
            {
                if (cklstColumns.GetItemChecked(i))
                    columnIndexes.Add(i + sourceSheet.UsedRange.Column);
            }

            if (columnIndexes.Count != cklstColumns.CheckedItems.Count)
            {
                MessageBox.Show("发生意外事故！选择的列的索引数目与选中项的个数竟然不一致！", "Excel", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }

            //选不选要查找的列结果都是一样的，但是如果选中了要查找的列，会多一列同样的内容。

            bool updateScreen = Globals.ThisAddIn.Application.ScreenUpdating;
            Globals.ThisAddIn.Application.ScreenUpdating = false;

            //在源表中找到用以查询的列
            Range destColumnRange = Globals.ThisAddIn.Application.Intersect(
                sourceSheet.Rows[cmbFindColumn.SelectedIndex + 1],
                sourceSheet.UsedRange
                );

            dataGridView1.Rows.Clear();//清空上次查询出来的数据。
            dataGridView1.ColumnCount = cklstColumns.CheckedItems.Count;
            dataGridView1.ColumnHeadersDefaultCellStyle.BackColor = Color.Navy;
            dataGridView1.ColumnHeadersDefaultCellStyle.ForeColor = Color.White;
            dataGridView1.ColumnHeadersDefaultCellStyle.Font =
                new System.Drawing.Font(dataGridView1.Font, FontStyle.Bold);

            for (int i = 0; i < cklstColumns.CheckedItems.Count; i++)
            {
                dataGridView1.Columns[i].Name = cklstColumns.CheckedItems[i].ToString();
            }

            var findedRowsCount = 0;
            char[] trimChars = new char[] { ' ', '　' };

            //因为查找比较耗时，为防止用户重复点击导致崩溃，禁用之。
            btnQuickFind.Enabled = btnFind.Enabled = false;

            foreach (string line in tbxQueryLines.Lines)
            {
                if (string.IsNullOrEmpty(line)) continue;

                for (int i = startRowIndex; i < sourceSheet.UsedRange.Row + sourceSheet.UsedRange.Rows.Count; i++)
                {
                    Range cell = sourceSheet.Cells[i, findColumnIndex];
                    if (cell == null || cell.Value == null) continue;
                    var cellText = cell.Value;
                    if (string.IsNullOrEmpty(cellText)) continue;

                    var a = cellText.Trim(trimChars);
                    var b = line.Trim(trimChars);
                    if (ckxIgnoreCase.Checked == true)
                    {
                        a = a.ToLower();
                        b = b.ToLower();
                    }

                    if (a == b)
                    {
                        //将整个一行数据（在UsedRange中）都添加到结果。
                        Range dataRow = Globals.ThisAddIn.Application.Intersect(sourceSheet.UsedRange,
                            cell.EntireRow);
                        string[] newRow = new string[cklstColumns.CheckedItems.Count];
                        findedRowsCount++;
                        for (int j = 0; j < cklstColumns.CheckedItems.Count; j++)
                        {
                            Range c = sourceSheet.Cells[i, columnIndexes[j]];
                            if (c == null || c.Value == null) continue;
                            newRow[j] = c.Value.ToString();
                        }

                        dataGridView1.Rows.Add(newRow);
                    }
                }
            }

            Globals.ThisAddIn.Application.ScreenUpdating = updateScreen;

            if (findedRowsCount > 0) tabControl1.SelectedIndex = 1;

            btnQuickFind.Enabled = btnFind.Enabled = true;

            MessageBox.Show($"找到 【{findedRowsCount}】 条数据！", "Excel", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }*/

        private void btnSelectAll_Click(object sender, EventArgs e)
        {
            for (int i = 0; i < cklstColumns.Items.Count; i++)
            {
                cklstColumns.SetItemChecked(i, true);
            }
        }

        private void btnUnSelectAll_Click(object sender, EventArgs e)
        {
            for (int i = 0; i < cklstColumns.Items.Count; i++)
            {
                cklstColumns.SetItemChecked(i, false);
            }
        }

        private void btnSwitchSelection_Click(object sender, EventArgs e)
        {
            for (int i = 0; i < cklstColumns.Items.Count; i++)
            {
                cklstColumns.SetItemChecked(i, !cklstColumns.GetItemChecked(i));
            }
        }

        private void textBox1_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter && e.Modifiers.HasFlag(Keys.Control))
            {
                btnQuickFind_Click(sender, e);
            }
            else if (e.KeyCode == Keys.A && e.Modifiers.HasFlag(Keys.Control))
            {
                tbxQueryLines.SelectAll();
            }
        }

        private void btnQuickFind_Click(object sender, EventArgs e)
        {
            if (sourceSheet == null)
            {
                MessageBox.Show("当前没有打开可供查询的工作表！", "Excel", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }

            if (sourceSheet.UsedRange.Count <= 0)
            {
                MessageBox.Show("选定的表中没有可供查询的数据！", "Excel", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }

            if (cklstColumns.CheckedItems.Count <= 0)
            {
                MessageBox.Show("请选中至少一列作为查找结果！", "Excel", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }

            //从哪一列开始，用户选择这一列必须准确，否则结果就不全。
            int startRowIndex = (int)numericUpDown1.Value + 2 - sourceSheet.UsedRange.Row;//标题行不算。
            int findColumnIndex = cmbFindColumn.SelectedIndex + 1;//这里要用到的数组索引从1开始算

            //先取出要出现在结果中的列标题
            List<int> columnIndexes = new List<int>();
            for (int i = 0; i < cklstColumns.Items.Count; i++)
            {
                if (cklstColumns.GetItemChecked(i))
                    columnIndexes.Add(i);
            }

            if (columnIndexes.Count != cklstColumns.CheckedItems.Count)
            {
                MessageBox.Show("发生意外事故！选择的列的索引数目与选中项的个数竟然不一致！", "Excel", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }

            //选不选要查找的列结果都是一样的，但是如果选中了要查找的列，会多一列同样的内容。

            bool updateScreen = Globals.ThisAddIn.Application.ScreenUpdating;
            Globals.ThisAddIn.Application.ScreenUpdating = false;

            //在源表中找到用以查询的源数据列
            Range destColumnRange = Globals.ThisAddIn.Application.Intersect(
                sourceSheet.Rows[cmbFindColumn.SelectedIndex + 1],
                sourceSheet.UsedRange
                );

            var queryFieldsCount = cmbQueryFeilds.Items.Count;

            dataGridView1.Rows.Clear();//清空上次查询出来的数据。
            dataGridView1.ColumnCount = cklstColumns.CheckedItems.Count + queryFieldsCount;            //加的是查询条件列
            dataGridView1.ColumnHeadersDefaultCellStyle.BackColor = Color.Black;
            dataGridView1.ColumnHeadersDefaultCellStyle.ForeColor = Color.White;
            dataGridView1.ColumnHeadersDefaultCellStyle.Font =
                new System.Drawing.Font(dataGridView1.Font, FontStyle.Bold);
            dataGridView1.RowHeadersWidthSizeMode = DataGridViewRowHeadersWidthSizeMode.AutoSizeToAllHeaders;

            dataGridView1.TopLeftHeaderCell.Value = "序号";

            //第0开始列是查询条件列。
            for (int i = 0; i < queryFieldsCount; i++)
            {
                if (ckxFstLineIsTitle.Checked)
                {
                    dataGridView1.Columns[i].Name = cmbQueryFeilds.Items[i].ToString();
                }
                else
                {
                    dataGridView1.Columns[i].Name = "[无列标题]";
                }
                dataGridView1.Columns[i].DefaultCellStyle.BackColor = Color.LightGray;
                dataGridView1.Columns[i].DefaultCellStyle.ForeColor = Color.Black;
            }

            //查询条件列右侧是结果列
            for (int i = 0; i < cklstColumns.CheckedItems.Count; i++)
            {
                var dataGridColumnIndex = queryFieldsCount + i;
                dataGridView1.Columns[dataGridColumnIndex].Name = cklstColumns.CheckedItems[i].ToString();
            }

            var findedRowsCount = 0;
            char[] trimChars = new char[] { ' ', '　' };

            //因为查找比较耗时，为防止用户重复点击导致崩溃，禁用之。
            btnQuickFind.Enabled = false;

            object[,] usedArray = sourceSheet.UsedRange.Value;

            int lineCount = 0;

            if (cmbQueryFeilds.Items.Count <= 0)
            {
                MessageBox.Show("请选择查询条件列！", "LunarExcelAddIn");
                return;
            }

            var fieldIndex = cmbQueryFeilds.SelectedIndex;

            var startLineIndex = (ckxFstLineIsTitle.Checked ? 1 : 0);
            var linesCount = tbxQueryLines.Lines.Length;

            for (int li = startLineIndex; li < linesCount; li++)
            {
                var line = tbxQueryLines.Lines[li];//.Trim()，这个不能删除首尾的空格。
                if (string.IsNullOrEmpty(line)) continue;

                lineCount++;//有效查找条件数目。

                string previewResult = null;
                int findedCount = 0;
                var fields = line.Split(new char[] { '\t' }, StringSplitOptions.None);

                if (fieldIndex >= fields.Length)
                {
                    continue;//找不到查询条件字段
                }

                var fieldText = fields[fieldIndex].Trim(trimChars);
                if (string.IsNullOrEmpty(fieldText))
                {
                    continue;//找不到查询条件字段（为空）
                }

                for (int i = startRowIndex; i <= usedArray.GetLength(0); i++)//这个数组的索引从1开始
                {
                    var ae = usedArray[i, findColumnIndex];
                    var cellText = (ae == null ? "" : ae.ToString());
                    if (string.IsNullOrEmpty(cellText)) continue;

                    var a = cellText.Trim(trimChars);
                    var b = fieldText;

                    if (ckxIgnoreCase.Checked == true)
                    {
                        a = a.ToLower();
                        b = b.ToLower();
                    }

                    if (a.StartsWith("\'")) a = a.Substring(1);
                    if (b.StartsWith("\'")) b = b.Substring(1);

                    if (a == b)
                    {
                        //将整个一行数据（在UsedRange中）都添加到结果。
                        //Range dataRow = Globals.ThisAddIn.Application.Intersect(sourceSheet.UsedRange,
                        //    cell.EntireRow);
                        string[] newRow = new string[queryFieldsCount + cklstColumns.CheckedItems.Count];

                        for (int f = 0; f < queryFieldsCount; f++)
                        {
                            if (f >= fields.Length) continue;
                            newRow[f] = fields[f];
                        }

                        findedRowsCount++;
                        for (int j = 0; j < cklstColumns.CheckedItems.Count; j++)
                        {
                            //Range c = sourceSheet.Cells[i, columnIndexes[j]];
                            //if (c == null || c.Value == null) continue;
                            //newRow[j] = c.Value.ToString();
                            var arrayElement = usedArray[i, columnIndexes[j] + 1];//这个数组索引是从1开始的
                            newRow[j + queryFieldsCount] = (arrayElement == null ? "" : arrayElement.ToString());
                        }

                        int rowIndex = dataGridView1.Rows.Add(newRow);

                        if (previewResult == cellText)
                        {
                            findedCount++;
                            dataGridView1.Rows[rowIndex].DefaultCellStyle.BackColor = Color.DarkGray;//重复
                            dataGridView1.Rows[rowIndex].HeaderCell.Value = "◆";
                            //或者用：dataGridView1.Rows.Count - 2。-2是因为最后一行是DataGridView自动添加的空行。
                        }
                        else
                        {
                            dataGridView1.Rows[rowIndex].HeaderCell.Value = $"{rowIndex + 1}";
                            findedCount = 1;
                        }

                        previewResult = cellText;
                    }
                }

                if (findedCount == 0)//如果没找到
                {
                    //string[] newRow = new string[cklstColumns.CheckedItems.Count + 1];
                    //newRow[0] = fieldText;
                    //newRow[1] = "<未找到>";

                    string[] newRow = new string[queryFieldsCount + cklstColumns.CheckedItems.Count];

                    for (int f = 0; f < queryFieldsCount; f++)
                    {
                        if (f >= fields.Length) continue;
                        newRow[f] = fields[f];
                    }

                    newRow[queryFieldsCount] = "<未找到>";
                    for (int j = 1; j < cklstColumns.CheckedItems.Count; j++)
                    {
                        newRow[j + queryFieldsCount] = "";
                    }

                    dataGridView1.Rows.Add(newRow);
                }

                findedCount = 0;
            }

            Globals.ThisAddIn.Application.ScreenUpdating = updateScreen;

            if (findedRowsCount > 0) tabControl1.SelectedIndex = 1;

            btnQuickFind.Enabled = true;

            MessageBox.Show($"　　有效查找条件共 【{lineCount}】 条，找到 【{findedRowsCount}】 条数据，请注意检查有无重复条目！",
                "Excel", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }

        private void btnPasteToNewSheet_Click(object sender, EventArgs e)
        {
            var newSheet = (Worksheet)Globals.ThisAddIn.Application.Sheets.Add(Globals.ThisAddIn.Application.ActiveSheet);

            newSheet.Cells.NumberFormatLocal = "@";

            //VBA Sample Code:
            //    Range("D14").Select
            //    ActiveSheet.PasteSpecial Format:= "HTML", Link:= False, DisplayAsIcon:= _
            //        False, NoHTMLFormatting:= True
            dataGridView1.SelectAll();

            var data = dataGridView1.GetClipboardContent();
            Clipboard.SetDataObject(data);

            Globals.ThisAddIn.Application.ScreenUpdating = false;

            newSheet.Cells[1, 1].Select();
            newSheet.PasteSpecial("HTML", false, false, null, null, null, true);
            //为什么使用这个方法？
            //因为这里是从 DataGridView 向 Excel 粘贴数据，而不是在 Excel 工作表之间粘贴数据。

            //VBA code...
            //With Selection.Interior
            //  .Pattern = xlSolid
            //  .PatternColorIndex = xlAutomatic
            //  .ThemeColor = xlThemeColorDark1
            //  .TintAndShade = -0.149998474074526
            //  .PatternTintAndShade = 0
            //End With

            var range = (Range)newSheet.Range[newSheet.Cells[1, 2], newSheet.Cells[dataGridView1.RowCount + 1, 2 + Math.Max(0, cmbQueryFeilds.Items.Count - 1)]];
            // dataGridView1.RowCount - 1 是因为它总是在最后有个空行！

            //range.Interior.Pattern = Microsoft.Office.Interop.Excel.Constants.xlSolid;
            //range.Interior.PatternColorIndex = Microsoft.Office.Interop.Excel.Constants.xlAutomatic;
            //range.Interior.TintAndShade = -0.149998474074526;
            //range.Interior.PatternTintAndShade = 0;
            range.Interior.ColorIndex = 20;   //ThemeColor = Microsoft.Office.Interop.Excel.XlThemeColor.xlThemeColorDark2;  //.xlThemeColorDark1;

            //range.Interior.ThemeColor = Microsoft.Office.Interop.Excel.XlThemeColor.xlThemeColorAccent2;
            //range.Font.ThemeColor = Microsoft.Office.Interop.Excel.XlThemeColor.xlThemeColorDark1;

            Globals.ThisAddIn.Application.ScreenUpdating = true;
        }

        private void tbxQueryLines_TextChanged(object sender, EventArgs e)
        {
            if (tbxQueryLines.Text == "")
            {
                cmbQueryFeilds.Items.Clear();
                return;
            }

            var fstLine = tbxQueryLines.Lines[0];
            cmbQueryFeilds.Items.Clear();

            var fields = fstLine.Split(new char[] { '\t' }, StringSplitOptions.None);

            foreach (var s in fields)
            {
                cmbQueryFeilds.Items.Add(s);
            }

            if (cmbQueryFeilds.Items.Count > 0)
            {
                cmbQueryFeilds.SelectedIndex = 0;
            }
        }

        private void btnClear_Click(object sender, EventArgs e)
        {
            tbxQueryLines.Clear();
        }
    }
}
